package c4.billing.cyclemanager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * This class is used to generate revenue report after each billing cycle.
 */

public class RevenueReporting {
	
	private static final String BILLING_PERIOD = "March08";

	public RevenueReporting(){
		Statement 	stmt = null;
		ResultSet 	rs = null;
		String 		query;
		double 		revenue = 0;
		
		try {
			/*
			 * Setup database objects
			 */
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			
		    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/corporate?" + 
		                                   "user=c4_user&password=p@ssw0rd");
		    
		    stmt = conn.createStatement();

		    /*
		     * Delete BILLING_PERIOD if it is already in revenue table
		     */
		    query = "DELETE FROM revenue WHERE period=\"" + BILLING_PERIOD + "\";";
		    
		    stmt.execute(query);
		    
		    /*
		     * Determine revenue by adding up all the bills that were sent out
		     */
			query = "SELECT bill FROM bills";

		    rs = stmt.executeQuery(query);
		    
		    while(rs.next()) {
		    	revenue += rs.getDouble("bill");
		    }
		    
		    /*
		     * Insert the revenue into the table for the BILLING_PERIOD
		     */
		    
		    query = "INSERT INTO revenue VALUES(\"" + BILLING_PERIOD + "\"," + revenue + ");";
		    
		    stmt.execute(query);
		}
		catch (SQLException ex) {
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
		catch (Exception e) {
			e.printStackTrace(System.out);
		}
	}
}
